"""
Author : Mr.Sun
Datetime : 2024/7/9 11:35 
FileName : SQLCipher_db.py
Desc :  这种可以支持密码,
        但是需要安装pysqlcipher3库
        pip install pysqlcipher3

尝试修改sqlite web 项目进行支持密码 和 网络服务能力
"""

import os
import dataset
from sqlalchemy import event
from sqlalchemy.engine import Engine
from pysqlcipher3 import dbapi2 as sqlcipher

project_dir = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
base_dir = os.path.join(project_dir, 'test_passwd_sqlcipher.db')
password = "12345"

# 创建数据库连接
db = dataset.connect(f"sqlite+pysqlcipher://:{password}@/{base_dir}", sqlite_wal_mode=False)


# 确保在每次连接时设置密码
@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute(f"PRAGMA key='{password}'")
    cursor.close()


print(db.tables)

# 示例：创建表并插入数据
table = db['test_table']
table.insert(dict(name='Alice', age=30))

# 查询数据
print(list(table.all()))
